package com.bluefox.system;

import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 * @Author : 清风冷影
 * @Description:
 * @Date : 2021/8/17  13:21
 */
public class test {
    public static void main(String[] args) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 获取当前日期
        Calendar calendar = Calendar.getInstance();
        // 河流首页 河流超标断面统计 以及 地图河流查询 查询时间由两天前的五个小时修改为查询当前时间的前八个小时
        calendar.add(Calendar.HOUR_OF_DAY, -8);//
        String time = sdf.format(calendar.getTime());
        int hour = calendar.get(Calendar.HOUR_OF_DAY);// 24小时制
        String dataFromTime = time + " " + (hour < 10 ? ("0" + hour) : hour) + ":00:00";
        String sql = "SELECT " +
                // start add by licy 添加断面状态查询 并且分组查询每个站点的最新数据 2017-2-27 18:30
                " ROW_NUMBER() over(PARTITION by t.C0007_SUBSTATION_ID order by t1.C1705_DATETIME desc) as num, s.C0201_TYPE_NAME ztlb,"
                // add by yuxia20170925 新增查询监测项的状态以及断面状态
                + " case when s.C0202_CODE ='311'  then s.C0202_NAME   end  as  nhStatus, "
                + " case  when  s.C0202_CODE ='316'   then  s.C0202_NAME   END AS codStatus,  "
                + " case when s.C0202_CODE = '314' THEN  s.C0202_NAME END AS mnStatus ,"
                + " case when s.C0202_CODE = '313' THEN  s.C0202_NAME END AS zlStatus ,"
                + " case when s.C0202_CODE = '466' THEN  s.C0202_NAME END AS zdStatus ,"
                + "    case when s.C0202_CODE != '311' and s.C0202_CODE != '316' and s.C0202_CODE != '314'  and s.C0202_CODE != '313' and s.C0202_CODE != '466'   then s.C0202_NAME   end as status, "
                +
                // + " s.C0202_NAME status," +
                // end by yuxia20170925 新增查询监测项的状态以及断面状态
                // end add by licy 添加断面状态查询 并且分组查询每个站点的最新数据2017-2-27 18:30
                "	2 type, " + "	t1.C1705_DATETIME AS aDate, " + "	t.C0003_STCODE, " + "	city.KH_CITYNAME, "
                + "	city.KH_CITYCODE, " + "	city.SS_CITYCODE, " + "	city.SS_CITYNAME, "
                + "	t.C0028_rcode, " + "	v.C0028_RNAME, " + "	t.C0007_SUBSTATION_ID stationId, "
                + "	t.C0007_PNAME, t.WQ_OBJECTIVE, " +
                // " t.C0007_LONGITUDE longtitude, " +
                // " t.C0007_LATITUDE latitude, " +
                "	t.BD_LONGITUDE longtitude, " + "	t.BD_LATITUDE latitude, " + "	t.GD_LONGITUDE gdlongtitude, "
                + "	t.GD_LATITUDE gdlatitude, " +

                "	t1.* FROM T_SUBSTATION_WATER t  LEFT JOIN (  SELECT  tAi.C0007_SUBSTATION_ID,hAvg.C1705_DATETIME, "
                + " MAX ( CASE  WHEN tItem.C0001_item_code = 302 THEN  hAvg.C1705_VALUE END ) AS 'PH', "
                + " MAX ( CASE  WHEN tItem.C0001_ITEM_CODE = 301 THEN  hAvg.C1705_VALUE END ) AS '水温', "
                + " MAX ( CASE  WHEN tItem.C0001_ITEM_CODE = 311 THEN  hAvg.C1705_VALUE END ) AS '氨氮', "
                // start add by yu_xia增加氨氮标准值2017-05-10
                + " MAX ( CASE  WHEN tItem.C0001_ITEM_CODE = 311 THEN std.C0113_THIS_AIM  END  ) AS 'n_stand', " +
                // end add by yu_xia 增加氨氮标准值2017-05-10
                " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 315 THEN hAvg.C1705_VALUE  END ) AS '溶解氧', "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 316 THEN  hAvg.C1705_VALUE END ) AS '化学需氧量', "
                // start add by yu_xia增加化学需氧量标准值2017-05-10
                +" MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 316 THEN std.C0113_THIS_AIM END  ) AS 'c_stand', " +
                // end add by yu_xia 增加化学需氧量标准值2017-05-10
                " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 350 THEN hAvg.C1705_VALUE END ) AS '湿度', "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 351 THEN  hAvg.C1705_VALUE END ) AS '室温', "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 463 THEN hAvg.C1705_VALUE END ) AS '电导率', "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 465 THEN hAvg.C1705_VALUE END ) AS '浊度', "
                // 高锰酸盐
                +" MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 314 THEN hAvg.C1705_VALUE END ) AS '高锰酸盐', "
                + " MAX (CASE WHEN tItem.C0001_ITEM_CODE = 314 THEN	std.C0113_THIS_AIM	END	) AS 'g_stand',"

                + " MAX(CASE WHEN tItem.C0001_ITEM_CODE = 313 THEN hAvg.C1705_VALUE END)                 AS '总磷',"
                + " MAX(CASE WHEN tItem.C0001_ITEM_CODE = 313 THEN std.C0113_THIS_AIM END)               AS 'zl_stand',"
                + " MAX(CASE WHEN tItem.C0001_ITEM_CODE = 466 THEN hAvg.C1705_VALUE END)                 AS '总氮',"
                + " MAX(CASE WHEN tItem.C0001_ITEM_CODE = 466 THEN std.C0113_THIS_AIM END)               AS 'zd_stand',"

                + " dbo.waterLevel (MAX (CASE WHEN tItem.C0001_ITEM_CODE = 316 THEN hAvg.C1705_VALUE END  ), "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 311 THEN  hAvg.C1705_VALUE END  ), "
                + " MAX ( CASE WHEN tItem.C0001_ITEM_CODE = 314 THEN  hAvg.C1705_VALUE END ), "

                +" MAX(CASE WHEN tItem.C0001_ITEM_CODE = 313 THEN hAvg.C1705_VALUE END), "
                +" MAX(CASE WHEN tItem.C0001_ITEM_CODE = 466 THEN hAvg.C1705_VALUE END),"
                +" MAX(CASE WHEN tItem.C0001_ITEM_CODE = 302 THEN hAvg.C1705_VALUE END),"
                +" MAX(CASE WHEN tItem.C0001_ITEM_CODE = 315 THEN hAvg.C1705_VALUE END) ) AS 'level' FROM T_RSECTION_HOUR_AVG hAvg "
                + "	LEFT JOIN T_AI tAi ON hAvg.C0008_PID = tAi.C0008_PID "
                + "	LEFT JOIN T_ITEM tItem ON tItem.C0001_ITEM_CODE = tAi.C0001_ITEM_CODE "
                + " LEFT JOIN  T_WATER_STAND_COUNT std on std.C0001_ITEM_CODE  = tItem.C0001_ITEM_CODE" +
                // " and std.C0003_YEAR='"+year+"' "
                " and std.C0007_SUBSTATION_ID = tAi.C0007_SUBSTATION_ID AND std.C0003_YEAR = DATENAME(YEAR,GETDATE())"
                + "	WHERE " +
                // start modify by licy 将查询最新时间的数据修改为某一天以后每个站点的最新数据 2017-2-27 18:30
                "		hAvg.C1705_DATETIME >='" + dataFromTime + "' "
                + "	AND datepart(hour,hAvg.C1705_DATETIME) in(0,4,8,12,16,20)" +
                // " hAvg.C1705_DATETIME = ( " +
                // " SELECT " +
                // " MAX (C1705_DATETIME) " +
                // " FROM " +
                // " T_RSECTION_HOUR_AVG where c1705_qcode = '0' " +
                // " ) " +
                // end modify by licy 将查询最新时间的数据修改为某一天以后每个站点的最新数据 2017-2-27 18:30
                "	AND hAvg.c1705_qcode = 0 " + "	GROUP BY " + "		tAi.C0007_SUBSTATION_ID ,hAvg.C1705_DATETIME "
                + ") t1 ON t1.C0007_SUBSTATION_ID = t.C0007_SUBSTATION_ID "
                + "LEFT JOIN WATER_CITY city ON city.SUBSTATION_ID = t.C0007_SUBSTATION_ID "
                + "LEFT JOIN T_River_Code v ON v.C0028_RCODE = t.C0028_rcode " +
                // start add by licy 添加断面状态查询2017-2-27 18:30
                "left join T_STATION_STATUS s on t.C0007_SUBSTATION_ID = s.C0007_SUBSTATION_ID and s.c0105_starttime <= GETDATE() and s.c0105_endtime >= GETDATE() "
                // + " and s.C0201_TYPE_NAME = '断面状态' " +
                + " and  ( s.C0201_TYPE_NAME = '断面状态'   or s.C0201_TYPE_NAME ='断面仪器状态'   or ( s.C0202_CODE = '311' "
                + " OR s.C0202_CODE = '316' " + " OR s.C0202_CODE = '314' OR s.C0202_CODE = '313' OR s.C0202_CODE = '466') ) " +
                // end add by licy 添加断面状态查询2017-2-27 18:30
                "WHERE " +
                // start add by licy 20181205 屏蔽掉断面：潍坊小清河入海口,青岛入海口（站点已撤销），莱芜的角峪（站点已撤销）
                "t.C0041_SUB_TYPE='2'  AND t.C0007_SUBSTATION_ID NOT IN ('3024','13092') and " +
                // end add by licy 20181205 屏蔽掉断面：潍坊小清河入海口,青岛入海口（站点已撤销），莱芜的角峪（站点已撤销）
                "	t.C0007_LONGITUDE IS NOT NULL " + "AND t.C0007_LATITUDE IS NOT NULL ";
        System.out.println(sql);
    }
}
